﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[fnConfig_IsDatabaseReadOnly]
#-- Purpose:		Is the database read only
#--	Last Update:	04/08/2016
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		[dbo].[spBkp_Log]
#--					[dbo].[spBkp_Differential]
#--					[dbo].[spMaint_ConsistencyCheck]
#--					[dbo].[spMaint_Indexes]
#------------------------------------------------------------------------------
*/
CREATE FUNCTION [dbo].[fnConfig_IsDatabaseReadOnly]
(
	@database_name sysname		= NULL
)
RETURNS BIT
AS
BEGIN
	--- Declare and Initialize Local Variables
	DECLARE @retVal bit
	SET @retVal = 0

	--- If no database name is passed in, set the Database Name to the current database
	IF ISNULL(@database_name, '') = ''
		SELECT	@database_name = DB_NAME()

	--- Return the Answer to the Question
	IF OBJECT_ID('[sys].[availability_replicas]') IS NULL
		SET @retVal = CAST(CASE	WHEN DATABASEPROPERTYEX(@database_name, 'Updateability') = 'READ_ONLY' THEN 1
								ELSE 0 
								END as bit)
	ELSE
		SET @retVal = CAST(CASE	WHEN DATABASEPROPERTYEX(@database_name, 'Updateability') = 'READ_ONLY' THEN 1
								WHEN DATABASEPROPERTYEX(@database_name, 'Updateability') IS NULL AND EXISTS (
									SELECT		TOP 1
												[replica_server_name],
												[secondary_role_allow_connections],
												[secondary_role_allow_connections_desc]
									FROM		[sys].[availability_replicas]
									WHERE		[replica_server_name] = @@servername
												AND (
													[replica_server_name] = (SELECT [primary_replica] FROM [sys].[dm_hadr_availability_group_states])
													OR (
														[secondary_role_allow_connections] > 0
													)
												)
									) THEN 1
								ELSE 0 
								END as bit)
	
	--- Return the value
	RETURN @retVal
END